package com.contest.repository;

import com.contest.entity.Course;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
    
    /**
     * 根据老师ID查找课程列表
     */
    List<Course> findByTeacherIdAndActiveTrue(Long teacherId);
    
    /**
     * 根据老师ID分页查找课程
     */
    Page<Course> findByTeacherIdAndActiveTrue(Long teacherId, Pageable pageable);
    
    /**
     * 查找所有活跃的课程
     */
    List<Course> findByActiveTrueOrderByCreatedAtDesc();
    
    /**
     * 分页查找所有活跃的课程
     */
    Page<Course> findByActiveTrueOrderByCreatedAtDesc(Pageable pageable);
    
    /**
     * 根据标题模糊查询活跃课程
     */
    List<Course> findByTitleContainingIgnoreCaseAndActiveTrue(String title);
    
    /**
     * 分页根据标题模糊查询活跃课程
     */
    Page<Course> findByTitleContainingIgnoreCaseAndActiveTrue(String title, Pageable pageable);
    
    /**
     * 查找学生已注册的课程
     */
    @Query("SELECT c FROM Course c JOIN c.students s WHERE s.id = :studentId AND c.active = true ORDER BY c.createdAt DESC")
    List<Course> findEnrolledCoursesByStudentId(@Param("studentId") Long studentId);
    
    /**
     * 分页查找学生已注册的课程
     */
    @Query("SELECT c FROM Course c JOIN c.students s WHERE s.id = :studentId AND c.active = true ORDER BY c.createdAt DESC")
    Page<Course> findEnrolledCoursesByStudentId(@Param("studentId") Long studentId, Pageable pageable);
    
    /**
     * 查找学生未注册的可用课程
     */
    @Query("SELECT c FROM Course c WHERE c.active = true AND c.id NOT IN " +
           "(SELECT ec.id FROM Course ec JOIN ec.students s WHERE s.id = :studentId) " +
           "ORDER BY c.createdAt DESC")
    List<Course> findAvailableCoursesForStudent(@Param("studentId") Long studentId);
    
    /**
     * 分页查找学生未注册的可用课程
     */
    @Query("SELECT c FROM Course c WHERE c.active = true AND c.id NOT IN " +
           "(SELECT ec.id FROM Course ec JOIN ec.students s WHERE s.id = :studentId) " +
           "ORDER BY c.createdAt DESC")
    Page<Course> findAvailableCoursesForStudent(@Param("studentId") Long studentId, Pageable pageable);
    
    /**
     * 检查学生是否已注册课程
     */
    @Query("SELECT COUNT(c) > 0 FROM Course c JOIN c.students s WHERE c.id = :courseId AND s.id = :studentId")
    boolean isStudentEnrolledInCourse(@Param("courseId") Long courseId, @Param("studentId") Long studentId);
    
    /**
     * 根据ID和老师ID查找课程（用于权限验证）
     */
    Optional<Course> findByIdAndTeacherId(Long id, Long teacherId);
    
    /**
     * 统计老师的课程数量
     */
    long countByTeacherIdAndActiveTrue(Long teacherId);
    
    /**
     * 统计课程的学生数量
     */
    @Query("SELECT COUNT(s) FROM Course c JOIN c.students s WHERE c.id = :courseId")
    long countStudentsByCourseId(@Param("courseId") Long courseId);
    
    /**
     * 查找包含指定章节数量的课程
     */
    @Query("SELECT c FROM Course c WHERE c.active = true AND SIZE(c.chapters) >= :minChapters ORDER BY c.createdAt DESC")
    List<Course> findCoursesWithMinimumChapters(@Param("minChapters") int minChapters);
}